﻿using CBS.DB;
using CBS.Model.ManageModel;
using CBS.Model.ViewModel;
using MySqlConnector;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CBS.APP
{
    public class StudentCostApp
    {
        public static List<StudentCostViewModel> GetList(int semesterId, string coursePlan, string keyword, Pagination page) 
        {
            string sql = "select a.*,b.SemesterName,c.CoursePlanName,d.CourseName,e.StartTime,f.StudentName\r\nfrom cbs_student_cost a\r\n\tleft join cbs_semester b on a.CostSemesterId = b.SemesterId\r\n\tleft join cbs_course_plan c on a.CostCoursePlanId = c.CoursePlanId\r\n\tleft join cbs_course d on c.CourseId = d.CourseId\r\n\tleft join cbs_lesson e on a.CostLessonId = e.LessonId\r\n\tleft join cbs_student f on a.StudentId = f.StudentId\r\nwhere a.CostSemesterId = @semesterId\r\nand (@coursePlan = '' or c.CoursePlanName like @coursePlan)\r\nand (@keyword = '' or f.StudentName like @keyword)";
            MySqlParameter[] para =
            {
                new MySqlParameter("@semesterId",semesterId),
                new MySqlParameter("@coursePlan",$"%{coursePlan}%"),
                new MySqlParameter("@keyword",$"%{keyword}%"),
            };

            return DBHelper.FindListOrderBy<StudentCostViewModel>(sql, page, FromPositionInSql.First, para);
        }
    }
}
